package gavdi.dk.data;

import gavdi.dk.model.Customer;

import java.util.Date;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {
	
  private static final String DATABASE_NAME = "timeregister.db";
  private static final int DATABASE_VERSION = 1;


  public static final String TABLE_ABSENCE = "Absence";
  public static final String TABLE_USER = "User";
  public static final String TABLE_CUSTOMER = "Customer";
  public static final String TABLE_RECENTWBS = "RecentWBS";
  public static final String TABLE_ASSIGNEDWBS = "AssignedWBS";
  public static final String TABLE_EVENTS = "Events"; 
  
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_NAME = "name";
  public static final String COLUMN_USERID = "userID";
  public static final String COLUMN_CUSID = "cusID";

  public static final String COLUMN_WBS = "wbs";
  public static final String COLUMN_DESCRIPTION = "description";
  public static final String COLUMN_PROJECTDEF = "projectdefinition";
  
  public static final String COLUMN_DATE = "date";
  public static final String COLUMN_STATUS = "status";
  public static final String COLUMN_COMMENT = "comment";
  public static final String COLUMN_TYPE = "type";
  public static final String COLUMN_HOURS = "hours";
  public static final String COLUMN_CODE = "code";
 
  public static final String COLUMN_CUSORABS = "cusorabs";
  
  private final static String createAbsenceTable = "create table " + TABLE_ABSENCE + 
		  "(" 
		  + COLUMN_NAME	      + " text, "
		  + COLUMN_CODE		  + " text primary key" +
		  "); ";
  // Creates the User table and its columns.
  private final static String createUserTable = "create table " + TABLE_USER + 
		  "(" 
		  + COLUMN_ID	      + " text primary key, " 
		  + COLUMN_NAME	      + " text " +
		  "); ";
  
  // Creates a Customer table and its columns
  private final  static String createCustomerTable = "create table " + TABLE_CUSTOMER + 
		  "(" 
		  + COLUMN_NAME	      	+ " text , "
		  + COLUMN_WBS 			+ " text primary key, "
		  + COLUMN_DESCRIPTION 	+  " text, "
		  + COLUMN_PROJECTDEF 	+  " text " +
		  "); ";
  
  // Creates a Assigned WBS table and its columns
  // Contains a ID and WBS element.
  // The ID can later on be linked to a user, within the tblUser table.
  private final  static String createAssignedWBSTable = "create table " + TABLE_ASSIGNEDWBS + 
		  "(" 
		  + COLUMN_ID	      	+ " integer primary key autoincrement, " 
		  + COLUMN_USERID 		+ " text, "
		  + COLUMN_WBS 		+  " text " +
		  "); ";
  
  // Creates a Recent WBS table and its columns
  // Contains a ID and WBS element.
  // The ID can later on be linked to a user, within the tblUser table.
  private final  static String createRecentWBSTable = "create table " + TABLE_RECENTWBS + 
		  "(" 
		  + COLUMN_ID	      	+ " integer primary key autoincrement, " 
		  + COLUMN_USERID 			+ " text, "
		  + COLUMN_WBS 	+  " text " +
		  "); ";
  
  // Creates a history of all events
  // User ID is only used, if you want to filter the history for a specific user
	
	private final  static String createEventsTable = "create table " + TABLE_EVENTS + 
			  "(" 
			  + COLUMN_ID	      		+ " integer primary key autoincrement, " 
			  + COLUMN_CUSORABS 		+ " text, " 
			  + COLUMN_USERID 			+ " text, "
			  + COLUMN_DATE 			+ " text, "
			  + COLUMN_STATUS 			+ " text, "
			  + COLUMN_COMMENT 			+ " text, "
			  + COLUMN_TYPE 			+ " text, "
			  + COLUMN_HOURS			+ " text " +
			  "); ";



  public MySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
 //   database.execSQL(DATABASE_CREATE);
	  database.execSQL(createUserTable);
      database.execSQL(createCustomerTable);
      database.execSQL(createAssignedWBSTable);
      database.execSQL(createRecentWBSTable);
      database.execSQL(createEventsTable);
      database.execSQL(createAbsenceTable);
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CUSTOMER);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECENTWBS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ASSIGNEDWBS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ABSENCE);
    onCreate(db);
  }

} 